const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

function generatePBSCode(level1, level2, level3) {
  // 生成PBS编码：区域-分析-子分项
  const area = level1.replace(/[^\u4e00-\u9fa5\w]/g, '').substring(0, 4);
  const analysis = level2 ? level2.replace(/[^\u4e00-\u9fa5\w]/g, '').substring(0, 4) : '';
  const subitem = level3.replace(/[^\u4e00-\u9fa5\w]/g, '').substring(0, 6);
  
  return analysis ? `${area}-${analysis}-${subitem}` : `${area}-${subitem}`;
}

function resetAndReimportPBS() {
  const csvPath = path.join(process.cwd(), 'excel', 'PBS层级结构-0709.csv');
  
  if (!fs.existsSync(csvPath)) {
    throw new Error(`CSV文件不存在: ${csvPath}`);
  }

  const csvContent = fs.readFileSync(csvPath, 'utf-8');
  const lines = csvContent.split('\n').filter(line => line.trim());
  
  const db = new Database(DB_PATH);
  
  try {
    console.log('开始重置PBS数据...');
    
    // 1. 清空现有PBS数据
    console.log('删除现有PBS层级数据...');
    db.exec('DELETE FROM instance_pbs_mapping');
    db.exec('DELETE FROM pbs_levels');
    
    // 重置自增ID
    db.exec("DELETE FROM sqlite_sequence WHERE name = 'pbs_levels'");
    db.exec("DELETE FROM sqlite_sequence WHERE name = 'instance_pbs_mapping'");
    
    console.log('现有PBS数据已清空');

    // 2. 重新导入数据
    const insertStmt = db.prepare(`
      INSERT INTO pbs_levels 
      (level1_area, level2_category, level3_subitem, full_path, pbs_code)
      VALUES (?, ?, ?, ?, ?)
    `);

    let currentArea = '';
    let currentCategory = '';
    let imported = 0;
    let skipped = 0;
    let skippedRecords = [];

    console.log('\n开始重新导入PBS数据...');

    for (let i = 0; i < lines.length; i++) {
      const line = lines[i];
      const lineNumber = i + 1;

      // 跳过表头
      if (line.includes('区域,分项,子分项')) {
        console.log(`第${lineNumber}行: [表头] 跳过`);
        continue;
      }

      // 解析CSV行
      const cleanLine = line.replace(/^\d+→/, ''); // 移除行号标记
      const columns = cleanLine.split(',').map(col => col.trim().replace(/^"|"$/g, ''));
      
      if (columns.length < 3) {
        skipped++;
        skippedRecords.push({
          lineNumber,
          reason: '列数不足',
          content: line.substring(0, 50)
        });
        console.log(`第${lineNumber}行: [跳过-列数不足] ${line.substring(0, 50)}...`);
        continue;
      }

      const [area, category, subitem] = columns;

      // 更新当前层级
      if (area && area !== '') {
        currentArea = area;
      }
      if (category && category !== '') {
        currentCategory = category;
      }

      // 必须有子分项（允许"-"）
      if (!subitem || subitem === '') {
        skipped++;
        skippedRecords.push({
          lineNumber,
          reason: '子分项为空',
          content: line.substring(0, 50),
          subitem
        });
        console.log(`第${lineNumber}行: [跳过-子分项为空] 子分项="${subitem}"`);
        continue;
      }

      const fullPath = currentCategory ? 
        `${currentArea} > ${currentCategory} > ${subitem}` : 
        `${currentArea} > ${subitem}`;
      
      const pbsCode = generatePBSCode(currentArea, currentCategory, subitem);

      try {
        insertStmt.run(currentArea, currentCategory || null, subitem, fullPath, pbsCode);
        imported++;
        
        if (imported <= 10 || imported % 50 === 0) {
          console.log(`第${lineNumber}行: [导入] ${fullPath} [${pbsCode}]`);
        }
      } catch (error) {
        skipped++;
        skippedRecords.push({
          lineNumber,
          reason: '数据库错误',
          content: line.substring(0, 50),
          error: error.message
        });
        console.warn(`第${lineNumber}行: [跳过-数据库错误] ${pbsCode} - ${error.message}`);
      }
    }

    console.log(`\n=== PBS数据重新导入完成 ===`);
    console.log(`总行数: ${lines.length}`);
    console.log(`导入成功: ${imported} 条`);
    console.log(`跳过记录: ${skipped} 条`);
    
    // 验证导入结果
    const countStmt = db.prepare('SELECT COUNT(*) as total FROM pbs_levels');
    const total = countStmt.get();
    console.log(`\n数据库中共有 ${total.total} 条PBS层级记录`);

    // 显示跳过的记录详情
    if (skippedRecords.length > 0) {
      console.log('\n=== 跳过的记录详情 ===');
      skippedRecords.forEach((record, index) => {
        console.log(`${index + 1}. 第${record.lineNumber}行: ${record.reason} - ${record.content}...`);
      });
    }

    // 显示按区域的统计
    console.log('\n=== 按区域统计 ===');
    const areaStmt = db.prepare(`
      SELECT level1_area, COUNT(*) as count 
      FROM pbs_levels 
      GROUP BY level1_area 
      ORDER BY count DESC
    `);
    const areaStats = areaStmt.all();
    areaStats.forEach(stat => {
      console.log(`- ${stat.level1_area}: ${stat.count}条`);
    });

    // 显示前5条记录作为验证
    console.log('\n=== 前5条PBS记录 ===');
    const sampleStmt = db.prepare('SELECT * FROM pbs_levels ORDER BY id LIMIT 5');
    const samples = sampleStmt.all();
    samples.forEach(record => {
      console.log(`${record.id}. ${record.full_path} [${record.pbs_code}]`);
    });

  } catch (error) {
    console.error('重置和导入PBS数据失败:', error);
    throw error;
  } finally {
    db.close();
  }
}

// 主执行函数
async function main() {
  try {
    console.log('🔄 开始重置并重新导入PBS层级结构数据...\n');
    
    resetAndReimportPBS();
    
    console.log('\n✅ PBS数据重置和重新导入完成！');
    
  } catch (error) {
    console.error('❌ 执行失败:', error);
    process.exit(1);
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  main();
}

module.exports = {
  resetAndReimportPBS
};